


ALTER      proc sp_order_schedhuling_dates
-- Input Parameter Marketing Part Number process step id
@mktg_part_number varchar(50)
-- @process_step_id int
as
 
declare @expected_date SMALLDATETIME 
declare @product_id int
declare @schedule_date SMALLDATETIME
declare @quantity int

declare @week1_Qty int
declare @week2_Qty int
declare @week3_Qty int
declare @week4_Qty int
declare @week5_Qty int
declare @week6_Qty int
declare @week7_Qty int
declare @week8_Qty int
declare @week9_Qty int
declare @week10_Qty int
declare @week11_Qty int
declare @week12_Qty int
declare @week13_Qty int
declare @week1_From  SMALLDATETIME
declare @week2_From  SMALLDATETIME
declare @week3_From  SMALLDATETIME
declare @week4_From  SMALLDATETIME
declare @week5_From  SMALLDATETIME
declare @week6_From  SMALLDATETIME
declare @week7_From  SMALLDATETIME
declare @week8_From  SMALLDATETIME
declare @week9_From  SMALLDATETIME
declare @week10_From SMALLDATETIME
declare @week11_From SMALLDATETIME
declare @week12_From SMALLDATETIME
declare @week13_From SMALLDATETIME

declare @week1_To  SMALLDATETIME
declare @week2_To  SMALLDATETIME
declare @week3_To  SMALLDATETIME
declare @week4_To  SMALLDATETIME
declare @week5_To  SMALLDATETIME
declare @week6_To  SMALLDATETIME
declare @week7_To  SMALLDATETIME
declare @week8_To  SMALLDATETIME
declare @week9_To  SMALLDATETIME
declare @week10_To SMALLDATETIME
declare @week11_To SMALLDATETIME
declare @week12_To SMALLDATETIME
declare @week13_To SMALLDATETIME

set @week1_Qty = 0
set @week2_Qty = 0
set @week3_Qty = 0
set @week4_Qty = 0
set @week5_Qty = 0
set @week6_Qty = 0
set @week7_Qty = 0
set @week8_Qty = 0
set @week9_Qty = 0
set @week10_Qty = 0
set @week11_Qty = 0
set @week12_Qty = 0
set @week13_Qty = 0


declare @results1 Table(week1 int,week2 int,week3 int,week4 int,week5 int,week6 int,week7 int,week8 int,week9 int,week10 int,week11 int,week12 int,week13 int)
declare @results2 Table(week1 int,week2 int,week3 int,week4 int,week5 int,week6 int,week7 int,week8 int,week9 int,week10 int,week11 int,week12 int,week13 int)


set @expected_date = getDate()
set @week1_From = @expected_date
set @week1_To = dateadd (day, 7, @week1_From)

set @week2_From = dateadd(day, 1, @week1_To)
set @week2_To = dateadd(day, 7, @week2_From)

set @week3_From = dateadd(day, 1, @week2_To)
set @week3_To = dateadd(day, 7, @week3_From)

set @week4_From = dateadd(day, 1, @week3_To)
set @week4_To = dateadd(day, 7, @week4_From)

set @week5_From = dateadd(day, 1, @week4_To)
set @week5_To = dateadd(day, 7, @week5_From)

set @week6_From = dateadd(day, 1, @week5_To)
set @week6_To = dateadd(day, 7, @week6_From)

set @week7_From = dateadd(day, 1, @week6_To)
set @week7_To = dateadd(day, 7, @week7_From)

set @week8_From = dateadd(day, 1, @week7_To)
set @week8_To = dateadd(day, 7, @week8_From)

set @week9_From = dateadd(day, 1, @week8_To)
set @week9_To = dateadd(day, 7, @week9_From)

set @week10_From = dateadd(day, 1, @week9_To)
set @week10_To = dateadd(day, 7, @week10_From)

set @week11_From = dateadd(day, 1, @week10_To)
set @week11_To = dateadd(day, 7, @week11_From)

set @week12_From = dateadd(day, 1, @week11_To)
set @week12_To = dateadd(day, 7, @week12_From)

set @week13_From = dateadd(day, 1, @week12_To)
set @week13_To = dateadd(day, 7, @week13_From)



/*print @week1_From
print @week1_To
print @week2_From
print @week2_To
print @week3_From
print @week3_To
print @week4_From
print @week4_To
print @week5_From
print @week5_To */


-- Get the Product ID for the given Marketing part number
select @product_id=Product_ID  from Product where P_Marketing_Part_Number = @mktg_part_number

--Create cursor for schedule dates and quantites for the productId
declare scheduleDateCursor CURSOR FOR
select COOL_Schedule_Date,COOL_Qty from Customer_Open_Order_LineItem where Product_ID =@product_id

open scheduleDateCursor 
fetch next from scheduleDateCursor into @schedule_date,@quantity
	while @@fetch_status = 0
	begin
		
	
	if(@schedule_date between @week1_From and @week1_To )
			begin
				set @week1_Qty =@week1_Qty+@quantity
			end
			else if(@schedule_date between @week2_From and @week2_To) 
			begin
				set @week2_Qty = @week2_Qty+@quantity
			end
			else if(@schedule_date between @week3_From and @week3_To) 
			begin		
				set @week3_Qty =@week3_Qty+@quantity
			end
			else if(@schedule_date between @week4_From and @week4_To ) 
			begin		
				set @week4_Qty =@week4_Qty+@quantity
			end
			else if(@schedule_date between @week5_From and @week5_To ) 
			begin		
				set @week5_Qty =@week5_Qty+@quantity
			end
			else if(@schedule_date between @week6_From and @week6_To )  
			begin		
				set @week6_Qty =@week6_Qty+ @quantity
			end
			else if(@schedule_date between @week7_From and @week7_To )  
			begin		
				set @week7_Qty = @week7_Qty+@quantity
			end
			else if(@schedule_date between @week8_From and @week8_To) 
			begin		
				set @week8_Qty =@week8_Qty+ @quantity
			end
			else if(@schedule_date between @week9_From and @week9_To ) 
			begin		
				set @week9_Qty =@week9_Qty+@quantity
			end
			else if(@schedule_date between @week10_From and @week10_To) 
			begin		
				set @week10_Qty = @week10_Qty+@quantity
			end
			else if(@schedule_date between @week11_From and @week11_To) 
			begin		
				set @week11_Qty =@week11_Qty+ @quantity
			end
			else if(@schedule_date between @week12_From and @week12_To) 
			begin		
				set @week12_Qty =@week12_Qty + @quantity
			end
			else if(@schedule_date between @week13_From and @week13_To) 
			begin		
				set @week13_Qty =@week13_Qty+ @quantity
			end


fetch next from scheduleDateCursor into @schedule_date,@quantity
end
close scheduleDateCursor
deallocate scheduleDateCursor


insert into @results1 (week1, week2, week3, week4, week5, week6, week7, week8, week9, week10, week11, week12, week13) values (isnull(@week1_Qty, 0),isnull(@week2_Qty, 0), isnull(@week3_Qty, 0), isnull(@week4_Qty, 0), isnull(@week5_Qty, 0), isnull(@week6_Qty, 0), isnull(@week7_Qty, 0), isnull(@week8_Qty, 0), isnull(@week9_Qty, 0), isnull(@week10_Qty, 0), isnull(@week11_Qty, 0), isnull(@week12_Qty, 0), isnull(@week13_Qty, 0)) 

set @week1_Qty = 0
set @week2_Qty = 0
set @week3_Qty = 0
set @week4_Qty = 0
set @week5_Qty = 0
set @week6_Qty = 0
set @week7_Qty = 0
set @week8_Qty = 0
set @week9_Qty = 0
set @week10_Qty = 0
set @week11_Qty = 0
set @week12_Qty = 0
set @week13_Qty = 0


-- Create cursor for request dates
declare requestDateCursor CURSOR FOR
select COOL_Requested_Date,COOL_Qty from Customer_Open_Order_LineItem where Product_ID =@product_id

open requestDateCursor 
fetch next from requestDateCursor into @schedule_date,@quantity
	while @@fetch_status = 0
	begin
		
	
	if(@schedule_date between @week1_From and @week1_To )
			begin
				set @week1_Qty =@week1_Qty+@quantity
			end
			else if(@schedule_date between @week2_From and @week2_To) 
			begin
				set @week2_Qty = @week2_Qty+@quantity
			end
			else if(@schedule_date between @week3_From and @week3_To) 
			begin		
				set @week3_Qty =@week3_Qty+@quantity
			end
			else if(@schedule_date between @week4_From and @week4_To ) 
			begin		
				set @week4_Qty =@week4_Qty+@quantity
			end
			else if(@schedule_date between @week5_From and @week5_To ) 
			begin		
				set @week5_Qty =@week5_Qty+@quantity
			end
			else if(@schedule_date between @week6_From and @week6_To )  
			begin		
				set @week6_Qty =@week6_Qty+ @quantity
			end
			else if(@schedule_date between @week7_From and @week7_To )  
			begin		
				set @week7_Qty = @week7_Qty+@quantity
			end
			else if(@schedule_date between @week8_From and @week8_To) 
			begin		
				set @week8_Qty =@week8_Qty+ @quantity
			end
			else if(@schedule_date between @week9_From and @week9_To ) 
			begin		
				set @week9_Qty =@week9_Qty+@quantity
			end
			else if(@schedule_date between @week10_From and @week10_To) 
			begin		
				set @week10_Qty = @week10_Qty+@quantity
			end
			else if(@schedule_date between @week11_From and @week11_To) 
			begin		
				set @week11_Qty =@week11_Qty+ @quantity
			end
			else if(@schedule_date between @week12_From and @week12_To) 
			begin		
				set @week12_Qty =@week12_Qty + @quantity
			end
			else if(@schedule_date between @week13_From and @week13_To) 
			begin		
				set @week13_Qty =@week13_Qty+ @quantity
			end


fetch next from requestDateCursor into @schedule_date,@quantity
end
close requestDateCursor
deallocate requestDateCursor


insert into @results2 (week1, week2, week3, week4, week5, week6, week7, week8, week9, week10, week11, week12, week13) values (isnull(@week1_Qty, 0),isnull(@week2_Qty, 0), isnull(@week3_Qty, 0), isnull(@week4_Qty, 0), isnull(@week5_Qty, 0), isnull(@week6_Qty, 0), isnull(@week7_Qty, 0), isnull(@week8_Qty, 0), isnull(@week9_Qty, 0), isnull(@week10_Qty, 0), isnull(@week11_Qty, 0), isnull(@week12_Qty, 0), isnull(@week13_Qty, 0)) 
select * from @results1
select * from @results2
	



